package com.example.shoppingmall.config;

import com.example.shoppingmall.pojo.*;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class PoiUtils {
    //导出
    //导出的话必先获取到所需要导出的数据
    public static ResponseEntity<byte[]> exportEmp2Excel(List<Commodity> commodities) {
        HttpHeaders headers = null;
        ByteArrayOutputStream baos = null;
        try {
            //1.创建Excel文档 new一个HSSFWorkbook对象
            HSSFWorkbook workbook = new HSSFWorkbook();
            //2.创建文档摘要
            workbook.createInformationProperties();
            //3.获取文档信息，并配置
            DocumentSummaryInformation dsi = workbook.getDocumentSummaryInformation();
            //3.1文档类别
            //通过一系列set方法设置信息
            dsi.setCategory("商品信息");
            //3.2设置文档管理员
            dsi.setManager("zhoudaiwei");
            //3.3设置组织机构
            dsi.setCompany("微信小商场");
            //4.获取摘要信息并配置
            SummaryInformation si = workbook.getSummaryInformation();
            //4.1设置文档主题
            si.setSubject("商品信息表");
            //4.2.设置文档标题
            si.setTitle("商品信息");
            //4.3 设置文档作者
            si.setAuthor("XXX集团");
            //4.4设置文档备注
            si.setComments("备注信息暂无");
            //创建Excel表单
            HSSFSheet sheet = workbook.createSheet("微信小商场商品信息表");
            //创建日期显示格式
            HSSFCellStyle dateCellStyle = workbook.createCellStyle();
            dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
            //创建标题的显示样式
            HSSFCellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
            headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            //定义列的宽度
            sheet.setColumnWidth(0, 5 * 256);
            sheet.setColumnWidth(1, 12 * 256);
            sheet.setColumnWidth(2, 10 * 256);
            //sheet.setColumnWidth(3, 5 * 256);
            sheet.setColumnWidth(3, 12 * 256);
            sheet.setColumnWidth(4, 20 * 256);
            sheet.setColumnWidth(5, 10 * 256);
            sheet.setColumnWidth(6, 10 * 256);
            sheet.setColumnWidth(7, 16 * 256);
            sheet.setColumnWidth(8, 12 * 256);
            sheet.setColumnWidth(9, 15 * 256);
            sheet.setColumnWidth(10, 20 * 256);
            sheet.setColumnWidth(11, 16 * 256);
            sheet.setColumnWidth(12, 14 * 256);
            sheet.setColumnWidth(13, 14 * 256);
            sheet.setColumnWidth(14, 12 * 256);
            sheet.setColumnWidth(15, 8 * 256);
            //5.设置表头
            HSSFRow headerRow = sheet.createRow(0);
            HSSFCell cell0 = headerRow.createCell(0);
            cell0.setCellValue("编号");
            cell0.setCellStyle(headerStyle);
            HSSFCell cell1 = headerRow.createCell(1);
            cell1.setCellValue("热门");
            cell1.setCellStyle(headerStyle);
            HSSFCell cell2 = headerRow.createCell(2);
            cell2.setCellValue("推荐");
            cell2.setCellStyle(headerStyle);
           /* HSSFCell cell3 = headerRow.createCell(3);
            cell3.setCellValue("图片");
            cell3.setCellStyle(headerStyle);*/
            HSSFCell cell4 = headerRow.createCell(3);
            cell4.setCellValue("名称");
            cell4.setCellStyle(headerStyle);
            HSSFCell cell5 = headerRow.createCell(4);
            cell5.setCellValue("排序");
            cell5.setCellStyle(headerStyle);
            HSSFCell cell6 = headerRow.createCell(5);
            cell6.setCellValue("标签");
            cell6.setCellStyle(headerStyle);
            HSSFCell cell7 = headerRow.createCell(6);
            cell7.setCellValue("销售价格");
            cell7.setCellStyle(headerStyle);
            HSSFCell cell8 = headerRow.createCell(7);
            cell8.setCellValue("成本价格");
            cell8.setCellStyle(headerStyle);
            HSSFCell cell9 = headerRow.createCell(8);
            cell9.setCellValue("市场价格");
            cell9.setCellStyle(headerStyle);
            HSSFCell cell10 = headerRow.createCell(9);
            cell10.setCellValue("商品分类");
            cell10.setCellStyle(headerStyle);
            HSSFCell cell11 = headerRow.createCell(10);
            cell11.setCellValue("商品类型");
            cell11.setCellStyle(headerStyle);
            HSSFCell cell12 = headerRow.createCell(11);
            cell12.setCellValue("品牌");
            cell12.setCellStyle(headerStyle);
            HSSFCell cell13 = headerRow.createCell(12);
            cell13.setCellValue("上下架");
            cell13.setCellStyle(headerStyle);
            HSSFCell cell14 = headerRow.createCell(13);
            cell14.setCellValue("库存");
            cell14.setCellStyle(headerStyle);
            HSSFCell cell15 = headerRow.createCell(14);
           /* cell15.setCellValue("创建时间");
            cell15.setCellStyle(headerStyle);
            HSSFCell cell16 = headerRow.createCell(16);*/
            //6.装数据 (赋值:createRow,createCell方法)
            //把查询出来的员工集合赋值入cell中
            for (int i = 0; i < commodities.size(); i++) {
                HSSFRow row = sheet.createRow(i + 1);
                Commodity commodity = commodities.get(i);
                row.createCell(0).setCellValue(commodity.getId());
                row.createCell(1).setCellValue(commodity.getHot());
                row.createCell(2).setCellValue(commodity.getRecommend());
                //row.createCell(3).setCellValue(commodity.getImgPath());
                /*      HSSFCell birthdayCell = row.createCell(4);*/
              /*  birthdayCell.setCellValue(commodity.getBirthday());
                birthdayCell.setCellStyle(dateCellStyle);*/
                row.createCell(3).setCellValue(commodity.getName());
                row.createCell(4).setCellValue(commodity.getSort());
                row.createCell(5).setCellValue(commodity.getLableId());
                row.createCell(6).setCellValue(commodity.getSalesPrice());
                row.createCell(7).setCellValue(commodity.getCostPrice());
                row.createCell(8).setCellValue(commodity.getMarketPrice());
                row.createCell(9).setCellValue(commodity.getClassifyId());
                row.createCell(10).setCellValue(commodity.getMoldId());
                row.createCell(11).setCellValue(commodity.getTrademarkId());
                row.createCell(12).setCellValue(commodity.getStatus());
                row.createCell(13).setCellValue(commodity.getRepertory());
                //row.createCell(15).setCellValue(commodity.getCreateTime());
                /*row.createCell(17).setCellValue(commodity.getSpecialty());
                row.createCell(18).setCellValue(commodity.getSchool());
                HSSFCell beginDateCell = row.createCell(19);
                beginDateCell.setCellValue(commodity.getBeginDate());
                beginDateCell.setCellStyle(dateCellStyle);
                row.createCell(20).setCellValue(commodity.getWorkState());
                row.createCell(21).setCellValue(commodity.getEmail());
                row.createCell(22).setCellValue(commodity.getContractTerm());
                HSSFCell beginContractCell = row.createCell(23);
                beginContractCell.setCellValue(commodity.getBeginContract());
                beginContractCell.setCellStyle(dateCellStyle);
                HSSFCell endContractCell = row.createCell(24);
                endContractCell.setCellValue(commodity.getEndContract());
                endContractCell.setCellStyle(dateCellStyle);*/
            }
            //最后创建一个ByteArrayOutputStream对象作为HSSFWorkbook的写入方法参数,执行write方法.
            headers = new HttpHeaders();
            //下载
            headers.setContentDispositionFormData("attachment",
                    new String("商品表.xls".getBytes("UTF-8"), "iso-8859-1"));
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            baos = new ByteArrayOutputStream();
            workbook.write(baos);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.CREATED);
    }

    //导入
    public static List<Commodity> importEmp2List(MultipartFile file) {
        List<Commodity> commodities = new ArrayList<>();
        try {
            HSSFWorkbook workbook =
                    new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
            int numberOfSheets = workbook.getNumberOfSheets();//获取每一页
            for (int i = 0; i < numberOfSheets; i++) {
                //通过该类的getSheetAt方法指定参数获取到文件中那一行的数据
                HSSFSheet sheet = workbook.getSheetAt(i);//获取第（i）个表
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();//多少行
                Commodity commodity;
                for (int j = 0; j < physicalNumberOfRows; j++) {
                    if (j == 0) {
                        continue;//标题行
                    }
                    HSSFRow row = sheet.getRow(j);
                    if (row == null) {
                        continue;//没数据
                    }
                    //
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();//单元格的个数
                    commodity = new Commodity();
                    for (int k = 0; k < physicalNumberOfCells; k++) {
                        HSSFCell cell = row.getCell(k);//具体到单元格的值
                        switch (cell.getCellTypeEnum()) {//类型
                            case STRING: {
                                String cellValue = cell.getStringCellValue();
                                if (cellValue == null) {
                                    cellValue = "";
                                }
                                switch (k) {
                                    case 1:
                                        commodity.setHot(Integer.parseInt(cellValue));
                                        break;
                                    case 2:
                                        commodity.setRecommend(Integer.parseInt(cellValue));
                                        break;
                                    case 3:
                                        commodity.setName(cellValue);
                                        break;
                                    case 4:
                                        commodity.setSort(Integer.parseInt(cellValue));
                                        break;
                                    case 5:
                                        commodity.setLableId(Integer.parseInt(cellValue));
                                        break;
                                    case 6:
                                        commodity.setSalesPrice(Double.parseDouble(cellValue));
                                        break;
                                    case 7:
                                        commodity.setCostPrice(Double.parseDouble(cellValue));
                                        break;
                                    case 8:
                                        commodity.setMarketPrice(Double.parseDouble(cellValue));
                                        break;
                                    case 9:
                                        commodity.setClassifyId(Integer.parseInt(cellValue));
                                        break;
                                    case 10:
                                        commodity.setMoldId(Integer.parseInt(cellValue));
                                        break;
                                    case 11:
                                        commodity.setTrademarkId(Integer.parseInt(cellValue));
                                        break;
                                    case 12:
                                        commodity.setStatus(Integer.parseInt(cellValue));
                                        break;
                                    case 13:
                                        commodity.setRepertory(Integer.parseInt(cellValue));
                                        break;
                                }
                            }
                            break;
                            case NUMERIC:{
                                Double cellValue = cell.getNumericCellValue();
                                switch (k) {
                                    case 1:
                                        commodity.setHot(cellValue.intValue());
                                        break;
                                    case 2:
                                        commodity.setRecommend(cellValue.intValue());
                                        break;
                                   /* case 3:
                                        commodity.setName(cellValue.intValue());
                                        break;*/
                                    case 4:
                                        commodity.setSort(cellValue.intValue());
                                        break;
                                    case 5:
                                        commodity.setLableId(cellValue.intValue());
                                        break;
                                    case 6:
                                        commodity.setSalesPrice(cellValue);
                                        break;
                                    case 7:
                                        commodity.setCostPrice(cellValue);
                                        break;
                                    case 8:
                                        commodity.setMarketPrice(cellValue);
                                        break;
                                    case 9:
                                        commodity.setClassifyId(cellValue.intValue());
                                        break;
                                    case 10:
                                        commodity.setMoldId(cellValue.intValue());
                                        break;
                                    case 11:
                                        commodity.setTrademarkId(cellValue.intValue());
                                        break;
                                    case 12:
                                        commodity.setStatus(cellValue.intValue());
                                        break;
                                    case 13:
                                        commodity.setRepertory(cellValue.intValue());
                                        break;
                                }
                            }
                            break;
                        }
                    }
                    commodities.add(commodity);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return commodities;
    }
}
